"""
SimpleAPI ORM 完全封装示例

这个示例展示了如何使用完全封装的 ORM 接口，用户无需了解任何 Tortoise ORM 的细节。
"""

import asyncio
from simpleapi.orm import (
    # 模型和字段
    Model, BaseModel,
    IntField, CharField, TextField, BooleanField, DateTimeField,
    ForeignKeyField, ManyToManyField,
    primary_key, created_at, updated_at, foreign_key,
    
    # 数据库
    Database, database, DatabaseConfig, with_database,
    
    # 查询
    Q, F, QueryBuilder, Aggregation,
    query_condition, field_expression, and_conditions, or_conditions,
    
    # 异常
    DoesNotExist, IntegrityError, ValidationError,
    
    # 迁移
    create_tables, make_migration, save_migration,
)


# 定义模型 - 使用我们封装的字段类型
class User(BaseModel):
    """用户模型 - 继承 BaseModel 自动获得 id, created_at, updated_at"""
    
    username = CharField(max_length=50, unique=True, description="用户名")
    email = CharField(max_length=100, description="邮箱")
    age = IntField(null=True, description="年龄")
    is_active = BooleanField(default=True, description="是否激活")
    
    class Meta:
        table = "users"
        
    def __str__(self):
        return f"User({self.username})"


class Category(BaseModel):
    """分类模型"""
    
    name = CharField(max_length=100, unique=True, description="分类名称")
    description = TextField(null=True, description="分类描述")
    
    class Meta:
        table = "categories"


class Post(BaseModel):
    """文章模型"""
    
    title = CharField(max_length=200, description="标题")
    content = TextField(description="内容")
    author = foreign_key("User", related_name="posts")
    category = foreign_key("Category", related_name="posts")
    tags = ManyToManyField("Tag", related_name="posts")
    is_published = BooleanField(default=False, description="是否发布")
    view_count = IntField(default=0, description="浏览次数")
    
    class Meta:
        table = "posts"


class Tag(BaseModel):
    """标签模型"""
    
    name = CharField(max_length=50, unique=True, description="标签名称")
    color = CharField(max_length=7, default="#000000", description="标签颜色")
    
    class Meta:
        table = "tags"


async def basic_operations_demo():
    """基础操作演示"""
    print("=== 基础操作演示 ===")
    
    # 连接数据库 - 自动发现模型
    await database.connect(
        url=DatabaseConfig.sqlite("orm_example.db"),
        generate_schemas=True
    )
    
    # 清空数据
    await User.truncate_table()
    await Category.truncate_table()
    await Post.truncate_table()
    await Tag.truncate_table()
    
    # 创建用户
    user = await User.create_record(
        username="alice",
        email="alice@example.com",
        age=25
    )
    print(f"✅ 创建用户: {user}")
    
    # 创建分类
    category = await Category.create_record(
        name="技术",
        description="技术相关文章"
    )
    print(f"✅ 创建分类: {category}")
    
    # 创建标签
    tag1 = await Tag.create_record(name="Python", color="#3776ab")
    tag2 = await Tag.create_record(name="ORM", color="#ff6b6b")
    print(f"✅ 创建标签: {tag1}, {tag2}")
    
    # 创建文章
    post = await Post.create_record(
        title="SimpleAPI ORM 使用指南",
        content="这是一篇关于如何使用 SimpleAPI ORM 的文章",
        author=user,
        category=category,
        is_published=True
    )
    print(f"✅ 创建文章: {post}")
    
    # 添加标签到文章（多对多关系）
    await post.tags.add(tag1, tag2)
    print("✅ 添加标签到文章")
    
    return user, category, post, tag1, tag2


async def query_operations_demo():
    """查询操作演示"""
    print("\n=== 查询操作演示 ===")
    
    # 基础查询
    users = await User.list_records(limit=10, order_by=["username"])
    print(f"📋 用户列表: {len(users)} 个用户")
    
    # 条件查询
    active_users = await User.list_records(is_active=True)
    print(f"👥 活跃用户: {len(active_users)} 个")
    
    # 使用查询构建器
    adult_users = await (User.query_builder()
                        .filter(age__gte=18)
                        .order_by("-age")
                        .all())
    print(f"🧑‍🦳 成年用户: {len(adult_users)} 个")
    
    # 复杂查询条件
    condition1 = query_condition(age__gte=20)
    condition2 = query_condition(is_active=True)
    complex_users = await (User.query_builder()
                          .filter(and_conditions(condition1, condition2))
                          .all())
    print(f"🔍 复杂查询用户: {len(complex_users)} 个")
    
    # 关联查询
    posts_with_author = await (Post.query_builder()
                              .select_related("author", "category")
                              .prefetch_related("tags")
                              .filter(is_published=True)
                              .all())
    print(f"📝 已发布文章: {len(posts_with_author)} 篇")
    
    # 聚合查询
    post_stats = await (Post.query_builder()
                       .annotate(
                           total_views=Aggregation.sum("view_count"),
                           avg_views=Aggregation.avg("view_count")
                       )
                       .values("total_views", "avg_views")
                       .first())
    print(f"📊 文章统计: {post_stats}")


async def advanced_operations_demo():
    """高级操作演示"""
    print("\n=== 高级操作演示 ===")
    
    # 批量创建用户
    users_data = [
        {"username": f"user{i}", "email": f"user{i}@example.com", "age": 20 + i}
        for i in range(1, 6)
    ]
    batch_users = await User.bulk_create_records(users_data)
    print(f"📦 批量创建用户: {len(batch_users)} 个")
    
    # 批量更新
    for user in batch_users:
        user.is_active = True
    await User.bulk_update_records(batch_users, ["is_active"])
    print("🔄 批量更新用户状态")
    
    # 事务操作
    from tortoise.transactions import in_transaction
    
    async def create_user_with_posts():
        async with in_transaction():
            user = await User.create_record(
                username="transaction_user",
                email="transaction@example.com"
            )
            
            for i in range(3):
                await Post.create_record(
                    title=f"事务文章 {i+1}",
                    content=f"这是第 {i+1} 篇事务文章",
                    author=user,
                    category_id=1  # 假设分类 ID 为 1
                )
            
            return user
    
    try:
        transaction_user = await create_user_with_posts()
        print(f"💾 事务创建用户: {transaction_user}")
    except Exception as e:
        print(f"❌ 事务失败: {e}")
    
    # 数据序列化
    user = await User.get_record(username="alice")
    if user:
        user_data = user.to_dict(exclude=["created_at", "updated_at"])
        print(f"📄 用户数据: {user_data}")
    
    # 原始 SQL 查询
    sql_results = await database.execute_sql(
        "SELECT username, email FROM users WHERE age > ?",
        [20]
    )
    print(f"🔧 原始 SQL 结果: {len(sql_results)} 条记录")


async def relationship_demo():
    """关系操作演示"""
    print("\n=== 关系操作演示 ===")
    
    # 获取用户及其文章
    user = await User.get_record(username="alice")
    if user:
        # 获取用户的所有文章
        user_posts = await Post.list_records(author=user)
        print(f"👤 用户 {user.username} 的文章: {len(user_posts)} 篇")
        
        # 使用关联查询
        posts_with_details = await (Post.query_builder()
                                   .filter(author=user)
                                   .select_related("category")
                                   .prefetch_related("tags")
                                   .all())
        
        for post in posts_with_details:
            print(f"📖 文章: {post.title}")
            print(f"   分类: {post.category.name if post.category else 'None'}")
            # 注意：tags 是多对多关系，需要特殊处理
            try:
                tags = await post.tags.all()
                tag_names = [tag.name for tag in tags]
                print(f"   标签: {', '.join(tag_names)}")
            except:
                print("   标签: 无")


@with_database(
    url=DatabaseConfig.sqlite("decorator_example.db"),
    generate_schemas=True
)
async def decorator_demo():
    """装饰器使用演示"""
    print("\n=== 装饰器使用演示 ===")
    
    # 直接使用，无需手动连接数据库
    user = await User.create_record(
        username="decorator_user",
        email="decorator@example.com",
        age=30
    )
    print(f"🎯 装饰器创建用户: {user}")
    
    return user


async def main():
    """主函数"""
    print("SimpleAPI ORM 完全封装示例")
    print("=" * 60)
    
    try:
        # 基础操作
        user, category, post, tag1, tag2 = await basic_operations_demo()
        
        # 查询操作
        await query_operations_demo()
        
        # 高级操作
        await advanced_operations_demo()
        
        # 关系操作
        await relationship_demo()
        
        # 装饰器演示
        await decorator_demo()
        
        print("\n🎉 所有演示完成！用户完全感知不到 Tortoise ORM！")
        
    except Exception as e:
        print(f"❌ 错误: {e}")
        import traceback
        traceback.print_exc()
    finally:
        # 关闭数据库连接
        await database.disconnect()
        print("✅ 数据库连接已关闭")


if __name__ == "__main__":
    asyncio.run(main()) 